﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DALLayer.Base;
using DataDefinition;

namespace DALLayer.Providers
{
    public class VehicleModelDataProvider : DataAccessBase
    {
        #region [Constructor]
        /// <summary>
        /// Initializes a new instance of the <see cref="VehicleModelDataProvider" /> class.
        /// </summary>
        public VehicleModelDataProvider() { }
        #endregion

        #region [Public Methods]
        /// <summary>
        /// Get vehicle model details by id or all vehicle models
        /// </summary>
        /// <param name="vehicleModelId">vehicle model id</param>
        /// <param name="selectById">select by id</param>
        /// <returns>data set</returns>
        public AutoFixDataSet GetVehicleModelDetails(int vehicleModelId, bool selectById)
        {
            AutoFixDataSet dataSet = null;

            try
            {
                //Triggers the method in the parent class DataAccessBase
                base.InitializeDataAccess();
                //Passes the sql string that needs to be added to the property in the parent class
                base.CommandTextBuilder.Append(@"SELECT VehicleModel.* FROM VehicleModel");
                //if its a selection by id
                if (selectById == true)
                {
                    //Add the sql string to retrieve data based on VehicleModelID
                    base.CommandTextBuilder.Append(@" WHERE (VehicleModelID = @VehicleModelID)");
                    //Add the params needed to retrieve data
                    base.DBParams.Add(@"@VehicleModelID", vehicleModelId);
                }

                bool success = false;
                //Check if it is success
                success = base.ExecuteReader();
                //If it is successful
                if (success == true)
                {
                    //Initial the dataset
                    dataSet = new AutoFixDataSet();
                    //Fill the table
                    dataSet.VehicleModel.Load(base.DataReader);
                }
            }
            catch (Exception eX)
            {
                // log the error here
            }
            finally
            {
                //Close connection
                base.FinalizeConnection();
            }
            //Return the filled dataset
            return dataSet;
        }
        /// <summary>
        /// Get vehicle model details by vehicle model id
        /// </summary>
        /// <param name="vehicleModelId">vehicle model id</param>
        /// <returns>dataSet</returns>
        public AutoFixDataSet VehicleModelDetailsByVehicleModelID()
        {
            AutoFixDataSet dataSet = null;

            try
            {
                //Triggers the method in the parent class DataAccessBase
                base.InitializeDataAccess();
                //Passes the sql string that needs to be added to the property in the parent class
                base.CommandTextBuilder.Append(@"SELECT VehicleModel.VehicleModelName, VehicleModelType.VehicleModelTypeName, VehicleModelTypeYear.VehicleYear, 
                         VehicleModelTypeYear.VehicleModelTypeImageURL, VehicleModelType.VehicleModelTypeID, VehicleModelTypeYear.VehicleModelTypeYearID, 
                         VehicleModel.VehicleModelID, VehicleModelType.IsActive");
                base.CommandTextBuilder.Append(@" FROM ((VehicleModelType INNER JOIN
                         VehicleModel ON VehicleModelType.VehicleModelID = VehicleModel.VehicleModelID) INNER JOIN
                         VehicleModelTypeYear ON VehicleModelType.VehicleModelTypeID = VehicleModelTypeYear.VehicleModelTypeID)");
              
                bool success = false;
                //Check if it is success
                success = base.ExecuteReader();
                //If it is successful
                if (success == true)
                {
                    //Initial the dataset
                    dataSet = new AutoFixDataSet();
                    //Fill the table
                    dataSet.VehicleModelsVehicleModelID.Load(base.DataReader);
                }
            }
            catch (Exception eX)
            {
                // log the error here
            }
            finally
            {
                //Close connection
                base.FinalizeConnection();
            }
            //Return the filled dataset
            return dataSet;
        }
        /// <summary>
        /// Get vehicle model details by article id
        /// </summary>
        /// <param name="articleID">articleID</param>
        /// <returns>dataSet</returns>
        public AutoFixDataSet GetVehicleModelDetailsByArticleID(int articleID)
        {
            AutoFixDataSet dataSet = null;

            try
            {
                //Triggers the method in the parent class DataAccessBase
                base.InitializeDataAccess();
                //Passes the sql string that needs to be added to the property in the parent class
                base.CommandTextBuilder.Append(@"SELECT VehicleModel.VehicleModelName, VehicleModelType.VehicleModelTypeName, VehicleModelTypeYear.VehicleYear, 
                         VehicleModelTypeYear.VehicleModelTypeImageURL, VehicleModel.VehicleModelID, VehicleModelType.VehicleModelTypeID, 
                         VehicleModelTypeYear.VehicleModelTypeYearID");
                base.CommandTextBuilder.Append(@" FROM (((VehicleModel INNER JOIN
                         VehicleModelType ON VehicleModel.VehicleModelID = VehicleModelType.VehicleModelID) INNER JOIN
                         VehicleModelTypeYear ON VehicleModelType.VehicleModelTypeID = VehicleModelTypeYear.VehicleModelTypeID) INNER JOIN
                         Article ON VehicleModel.VehicleModelID = Article.VehicleModelID AND VehicleModelType.VehicleModelTypeID = Article.VehicleModelTypeID)");
                base.CommandTextBuilder.Append(@"WHERE (VehicleModel.IsActive = True) AND (VehicleModelType.IsActive = True) AND (VehicleModelTypeYear.IsActive = True) AND (Article.ArticleID = @ArticleID)");

                //Add the params needed to retrieve data
                base.DBParams.Add(@"@ArticleID", articleID);

                bool success = false;
                //Check if it is success
                success = base.ExecuteReader();
                //If it is successful
                if (success == true)
                {
                    //Initial the dataset
                    dataSet = new AutoFixDataSet();
                    //Fill the table
                    dataSet.GetVehicleDetailsArticleID.Load(base.DataReader);
                }
            }
            catch (Exception eX)
            {
                // log the error here
            }
            finally
            {
                //Close connection
                base.FinalizeConnection();
            }
            //Return the filled dataset
            return dataSet;
        }

       /// <summary>
        /// The method inserts vehicle to the table
       /// </summary>
       /// <param name="vehicleModelName">vehicle model name</param>
        /// <param name="isActive">is active</param>
       /// <returns>true or false</returns>
        public bool InsertVehicleModel(string vehicleModelName, bool isActive)
        {

            bool success = false;
            try
            {
                //Triggers the method in the parent class DataAccessBase
                base.InitializeDataAccess();
                //Passes the sql string that needs to be added to the property in the parent class

                base.CommandTextBuilder.Append(@"
                    INSERT INTO VehicleModel(VehicleModelName, IsActive)
                    VALUES(@VehicleModelName, @IsActive)");
                //Add the params needed to retrieve data
                base.DBParams.Add(@"@VehicleModelName", vehicleModelName);
                base.DBParams.Add(@"@IsActive", isActive);
                //Check if it is success
                success = base.ExecuteNonQuery();

            }
            catch (Exception)
            {
                success = false;
                //Log here
            }
            //return success
            return success;
        }
        /// <summary>
        /// The method inserts vehicle to the table and returns the identity
        /// </summary>
        /// <param name="vehicleModelName">vehicle model name</param>
        /// <param name="isActive">is active</param>
        /// <returns>int</returns>
        public int InsertVehicleModelScalar(string vehicleModelName, bool isActive)
        {

            int result = 0;
            try
            {
                //Triggers the method in the parent class DataAccessBase
                base.InitializeDataAccess();
                //Passes the sql string that needs to be added to the property in the parent class

                base.CommandTextBuilder.Append(@"
                    INSERT INTO VehicleModel(VehicleModelName, IsActive)
                    VALUES(@VehicleModelName, @IsActive)");
                //Add the params needed to retrieve data
                base.DBParams.Add(@"@VehicleModelName", vehicleModelName);
                base.DBParams.Add(@"@IsActive", isActive);
                //get the result                
                result = base.ExecuteScalar();

            }
            catch (Exception)
            {
                //Log here
            }
            finally
            {
                base.FinalizeConnection();
            }
            //return success
            return result;
        }

        /// <summary>
        /// The method updates vehicle model
        /// </summary>
        /// <param name="vehicleModelID">vehicle model id</param>
        /// <param name="vehicleModelName">vehicle model name</param>
        /// <param name="isActive">is active</param>
        /// <returns>true or false</returns>
        public bool UpdateVehicleModel(int vehicleModelID, string vehicleModelName, bool isActive)
        {
            bool success = false;
            try
            {
                //Triggers the method in the parent class DataAccessBase
                base.InitializeDataAccess();
                //Passes the sql string that needs to be added to the property in the parent class

                base.CommandTextBuilder.Append(@"
                    UPDATE VehicleModel
                    SET VehicleModelName = @VehicleModelName,
                        IsActive = @IsActive                       
                        WHERE VehicleModelID = @VehicleModelID");
                //Add the params needed to retrieve data
                base.DBParams.Add(@"@VehicleModelID", vehicleModelID);
                base.DBParams.Add(@"@VehicleModelName", vehicleModelName);
                base.DBParams.Add(@"@IsActive", isActive);

                //Check if it is success
                success = base.ExecuteNonQuery();

            }
            catch (Exception)
            {
                success = false;
                //Log here
            }
            //return success
            return success;

        }
        #endregion
    }
}
